The present invention relates generally to accessing information stored on a computer, and more particularly to efficiently retrieving information from multiple databases.
As companies increasingly create and store large amounts of information in electronic form, computer databases (DBs) play an increasingly important role in everyday business operations. In many situations, a company will have many different databases (e.g., payroll, sales, and manufacturing schedules) with information that needs to be accessible to a variety of individuals throughout the company. At the same time, some information is sensitive (e.g., payroll and cost information), and must be accessible only to those individuals who have been granted authorization to access it. Thus, managing access to database information is an important aspect of business operations.
In a typical company with a computer system that includes multiple computer databases, each computer user will gain access to the computer system (usually a network of computers) using a unique user ID and an individual computer password. Once a user has gained access to the computer system, the user can generally access documents, execute application programs, and utilize computer system resources (e.g., a network printer). In addition, the user may be able to extract information from one or more databases. This will typically be accomplished by executing database queries on a database server for one or more databases.
Database queries can be executed using report forms and inquiries. For any particular database, users or system administrators will generally have created a variety of database report forms that can be used to extract information from that database. Each report form specifies a particular group of information in a database, and when the report form is executed on the database a report is generated containing information extracted from the database. For example, a Year-To-Date Engineering Payroll Report Form may be created for a payroll database, and when executed it will extract the payroll information for people in the engineering group from the beginning of the year until the time of execution. Other report forms include user-definable options, called report parameters, that can adjust the group of information specified by the report. For example, a Periodic Engineering Payroll Report Form may also be created for the payroll database, with the user able to specify start and end date parameters to define a period of time for which corresponding engineering group payroll information will be extracted. In addition to the predefined report forms which a user can execute on a database, most databases will also allow a user to specify a free form inquiry that can be executed on the database. These inquiries will generally be specified using some form of a database query language (e.g., SQL).
Despite the availability of report forms and inquiries, the actual process of extracting desired information from one of several databases can be cumbersome. The user will first need to identify the particular database which contains desired information. In order to execute a query (i.e., a report form or inquiry) on the identified database, the user will then typically need to gain access to the database. For example, the user may need to specify connection information (e.g., the network location of the database server) to be able to communicate with the database. In addition, the user may have a distinct database user ID and database password for each database, and if so the correct database user ID and database password may need to be specified before the user is allowed access to a database.
After the user has succeeded in gaining access to the database, the user must either manually specify a mistake-free inquiry or locate a predefined report form for the database which specifies the appropriate information, allowing execution of the query to retrieve the desired information. If the user attempts to execute a located or specified query which they do not have authorization to execute, execution will not be allowed. There are also situations in which a specific query (i.e., a specific inquiry or a report form with specific parameters) will need to be executed repeatedly (e.g, every morning) or at a non-convenient time (e.g., at 2 a.m.). The need to repeatedly enter or manually execute a specific query can also be cumbersome. As companies grow and have more databases which must be accessed, this process of extracting desired information becomes even more cumbersome.
In addition to the cumbersome aspects of a single user extracting database information, interactions of multiple users can further complicate the process of extracting desired information from databases. As with any computing system, a database server has finite processing power and can be overloaded by too many queries. In particular, high-impact queries can consume significant database server processing power and their execution can be quite time-consuming (e.g., 5 or more minutes). If one user is already executing a high-impact query, another user may be unable to execute another query at the same time. Alternately, database server processing power can be wasted by one user executing a query for which up-to-date results are already available from a previously executed query.
Another complication which can occur involves the storage of information that is extracted from a database in response to a query. Some database servers store all extracted information in the same location, and a database server may store only one copy of results for each report form. Thus, if a first user executes a report form with one set of parameters and another user executes the same report form with a second set of parameters soon afterwards, the results from the second query may overwrite the results from the first query before the first user can access them. Thus, the first user would receive results that were based on different report form parameters, as well as possibly receiving more up-to-date information than was desired. Thus, extracting information from multiple databases in a multi-user environment presents significant difficulties.
Some embodiments of the present invention provide a method and system for efficiently retrieving information from one of several databases. The system acts as an intermediary between users and the databases, managing user access to the databases so that query specification, query execution, and query result retrieval can occur efficiently. After verifying the identity of a user, the system determines the databases which the user is authorized to access and the database queries which the user is authorized to execute. This can be accomplished for each user, or on the basis of one or more groups to which the user belongs. The system then presents the user with available queries. If the user selects a predefined report form, the system presents the user with previously used parameters for that report form which the user can select. Alternately, the user can specify new report form parameters. If the user selects a type of inquiry, the system presents the user with previously used inquiries of the selected type. When the selected query is to be executed, the system determines whether current execution is possible and whether it is actually necessary to execute the query to return accurate query results. If current report results are already available, the system returns the results without executing the query. If execution is needed to obtain accurate query results and the selected query can be currently executed, the system automatically performs the query execution. If current query execution is needed but not available, the system can alert the user to try again later. After query execution is completed, the system notifies specified users of the query results, and ensures that the query results will be available to the requesting user and to other users.
Thus, the present invention provides various advantages over previous systems. In one embodiment, the system ensures secure access of database information by verifying the identity of a user and allowing the user to execute only authorized queries. In another embodiment, the system minimizes the load on a database server by managing the execution of queries, executing queries only if current query results are not available, and postponing execution of a query while the database server load is too high. In yet another embodiment, the system ensures efficient access to multiple databases by presenting all authorized queries to the user, by presenting the user with previously used report form parameters and inquiries, by automatically performing the query execution, and by notifying users of the query results.